#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb
import time
import sys
import types

reload(sys)
sys.setdefaultencoding("utf-8")

def createTable(tableName, cursor) :
    # connect to database peersStat
    # db = MySQLdb.connect("localhost", "root", "123456", "peersStat")

    # get database cursor 
    # cursor = db.cursor() 

    # delete table if it exits
    # cursor.execute("DROP TABLE IF EXISTS " + tableName)

    sql = "CREATE TABLE IF NOT EXISTS %s (   \
            comment varchar(30) NOT NULL,   \
            peerSum int(20) NOT NULL,   \
            time datetime NOT NULL  \
            ) DEFAULT CHARSET=utf8" % tableName

    cursor.execute(sql)

    # close connection
    # db.close()


def queryRoute() :
    ret = None
    db = MySQLdb.connect("localhost", "root", "123456", "p2p")
    cursor = db.cursor()
    cursor.execute("SET NAMES 'utf8'")
    select_sql = "SELECT DISTINCT(id), comment FROM route"
    print select_sql
    try :
        cursor.execute(select_sql)
        ret = cursor.fetchall()
    except :
        print "Error: fetch data failed"

    db.close()
    return ret


def insertData(tableName, comment, peerSum, time) :
    db = MySQLdb.connect("localhost", "root", "123456", "peersStat")
    cursor = db.cursor()
    createTable(tableName, cursor)
    db.set_character_set("utf8")
    cursor.execute("SET NAMES 'utf8'")
    cursor.execute("SET CHARACTER SET 'utf8'")
    cursor.execute("SET character_set_connection='utf8'")
    insert_sql = "INSERT INTO %s (comment, peerSum, time) VALUES ('%s', '%d', '%s')" % \
                    (tableName, comment, peerSum, time)
    # print insert_sql
    try :
        cursor.execute(insert_sql)
        db.commit()
    except :
        print "insert error"
        db.rollback()

    db.close()


if __name__ == "__main__" :
    list = queryRoute()
    print list[0][1]
    comment = "华北地区 中国电信"
    insertData("pythonTestTable", comment, 20, time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()))
